#ifndef JPMYSQLLIB_H
#define JPMYSQLLIB_H
#include<mysql/mysql.h>
#include <libgen.h>
#include<globalfunc.h>
#include<stdlib.h>
#include<jpmd5.h>
#define DB_HOST "localhost"  //数据库地址
#define DB_DATABASE "db_image"  //数据库名称
#define DB_USER "root" //数据库用户名
#define DB_PWD "wjp"  //数据库密码
MYSQL *mysqlcon;  //连接mysql资源
/********************连接mysql***************************/
void jpmysql_open(){
    mysqlcon=mysql_init(NULL);
    if(!mysqlcon)
    {
        printf("初始化mysql失败\n");
        return ;
    }
    mysqlcon = mysql_real_connect(mysqlcon,DB_HOST,DB_USER,DB_PWD,DB_DATABASE,3306,NULL,CLIENT_MULTI_STATEMENTS);
    if(mysqlcon){
        mysql_select_db(mysqlcon,DB_DATABASE);
        mysql_query(mysqlcon,"SET NAMES UTF8");
        //return mysqlcon;
    }else{
        printf("连接mysql失败\n");
        return ;
    }

}

/********************断开mysql***************************/
void jpmysql_close(){
   mysql_close(mysqlcon);
}
/*****************************执行mysql语句**********************************/
/******************************参数：1：sql语句，返回值，执行结果*********************************/
int jp_query(char *sql){
    return mysql_query(mysqlcon,sql);
}

/********************往mysql插入操作***************************/
/******************************参数：1表名，2字段名，3对应字段的值,执行成功返回0，否则返回1*******************************/
int jp_insert(char *tablename,char *field,char *values){
   int len=strlen(tablename);
   len+=strlen(field)+strlen(values);
   char *sql=(char *)malloc(len+50);
   sprintf(sql,"insert into %s(%s) values(%s)",tablename,field,values);

   int res=jp_query(sql);

   free(sql);
   return res;
}
/********************往mysql更新操作***************************/
/******************************参数：1表名，2字段名=字段值，3更新条件,执行成功返回0，否则返回1*******************************/
int jp_update(char *tablename,char *set,char *where){
    if(where==NULL){
        where="1=1";
    }
    int len=strlen(tablename);
    len+=strlen(set)+strlen(where);
    char *sql=(char *)malloc(len+50);
    sprintf(sql,"update %s set %s where %s",tablename,set,where);

    int res=jp_query(sql);
    free(sql);
    return res;
}
/********************往mysql删除操作***************************/
/******************************参数：1表名，3删除条件,执行成功返回0，否则返回1*******************************/
int jp_delete(char *tablename,char *where){
    if(where==NULL){
        where="1=1";
    }
    int len=strlen(tablename);
    len+=strlen(where);
    char *sql=(char *)malloc(len+50);
    sprintf(sql,"delete from %s where %s",tablename,where);

    int res=jp_query(sql);
    free(sql);
    return res;
}
/********************往mysql查询操作***************************/
/******************************参数：1查询条件,执行成功返回查询资源句柄，否则返回NULL*******************************/
MYSQL_RES * jp_select(char *sql){
    int con=mysql_query(mysqlcon,sql);
    if(con){
        printf("查询失败%s\n",mysql_error(mysqlcon));
        return NULL;
    }
    MYSQL_RES *res=mysql_store_result(mysqlcon);
    return res;

}
/***************************判断路径是否存在数据库中,不存在则添加*****************************/
/******************************参数：1图片路径*******************************/
void check_image_path(char *image_path,int _type,bool is_update=false){
    jpmysql_open();
    char *codeno=get_codeno(image_path);
    char *filename=basename(image_path);
    char md5[50];
    jpmd5(image_path,md5);
    char *pre="select count(*) from jdx_image_info where md5='";

    char select_sql[2048]={0};
    sprintf(select_sql,"%s%s'",pre,md5);

    MYSQL_RES *rs=jp_select(select_sql);
    if(rs){
    MYSQL_ROW row=mysql_fetch_row(rs);
    int count=atoi(row[0]);

    if(count>0){
        if(is_update){

             char update_sql_set[2048]={0};
             char update_sql_where[2048]={0};



             sprintf(update_sql_set,"path='%s',codeno='%s',filename='%s',md5='%s',type=%d",image_path,codeno,filename,md5,_type);
             sprintf(update_sql_where,"path='%s' and type=%d",image_path,_type);
            int rt=jp_update("jdx_image_info",update_sql_set,update_sql_where);
            //printf("执行更新:%d\n",rt);

        }
       // printf("%s存在\n",image_path);

    }else{

        char insert_sql[2048]={0};


        sprintf(insert_sql,"'%s','%s','%s','%s',%d",image_path,codeno,filename,md5,_type);
        int rt=jp_insert("jdx_image_info","path,codeno,filename,md5,type",insert_sql);
        //printf("执行插入:%d\n",rt);
    }

        mysql_free_result(rs);

    }


    if(codeno){
    free(codeno);
    }
    jpmysql_close();
}
/***********************************************
 * 查询是否计算过图片相似度，计算过返回true，否则返回false
 * 参数1：远程图片路径
 * 参数2：本地图片路径
 * 返回值：计算过返回true，否则返回false
 * ******************************************/
bool is_hist(char *image1,char *image2){
    jpmysql_open();
    char md5_remote[50],md5_local[50];
    jpmd5(image1,md5_remote);
    jpmd5(image2,md5_local);
    char *pre="select count(*) from jdx_image_info as rinfo right join jdx_image_comp as comp on rinfo.id=comp.remote_image_id and rinfo.type=1 left join jdx_image_info as cinfo on comp.image_id=cinfo.id and cinfo.type=0";
    char sql[2048]={0};
    sprintf(sql,"%s where rinfo.md5='%s' and cinfo.md5='%s'",pre,md5_remote,md5_local);
    MYSQL_RES *rs=jp_select(sql);
    if(rs){
        MYSQL_ROW row=mysql_fetch_row(rs);

        int count=atoi(row[0]);

        if(count>0){
             mysql_free_result(rs);
            jpmysql_close();

            return true;
        }
    }
     mysql_free_result(rs);
    jpmysql_close();
    return false;

}
/**************************************************
 *获得图片信息id
 * 参数1：图片路径md5
 * 返回值：图片id
*************************************************/
int get_image_id(char *md5,int type){
    char sql[1024]={0};
    int id=0;
    sprintf(sql,"select id from jdx_image_info where md5='%s' and type=%d",md5,type);
    MYSQL_RES *rs=jp_select(sql);
    if(rs){
        MYSQL_ROW row=mysql_fetch_row(rs);
        id=atoi(row[0]);
    }
    mysql_free_result(rs);
    return id;

}

/***************************
 * 把计算图片相似度结果存入数据库中，
 * 判断远程图和本地图是否计算过图片相似度，如果没计算过，则新加一条计算记录到数据库中。如果计算过，则插入
 * 参数：1远程传送过来的图片路径
 * 参数2：本地图片路径
 * 参数3：相似度值
*****************************/
void hist_to_db(char *image1,char *image2,double *hist){
    jpmysql_open();
    char insert_sql[2048];
    char md5_remote[50],md5_local[50];
    jpmd5(image1,md5_remote);
    jpmd5(image2,md5_local);
    int rid=get_image_id(md5_remote,1);
    int cid=get_image_id(md5_local,0);
    sprintf(insert_sql,"%d,%d,%f,%f,%f,%f",cid,rid,hist[0],hist[1],hist[2],hist[3]);
    //int res=jp_insert("jdx_image_comp","image_id,remote_image_id,hist1,hist2,hist3,hist4",insert_sql);
    //printf("插入比较结果:%d\n",res);
    jp_insert("jdx_image_comp","image_id,remote_image_id,hist1,hist2,hist3,hist4",insert_sql);
    jpmysql_close();
}
/*********************************************************
 *取出top20最相似的图片
 * 参数1：远程图片路径
 *
*********************************************************/
void get_top_20(char *remote_image,char *orderby){
    jpmysql_open();
    char md5_remote[50];
    jpmd5(remote_image,md5_remote);
    char *pre="select rinfo.path,rinfo.filename,cinfo.path,cinfo.filename,comp.hist1,comp.hist2,comp.hist3,comp.hist4 from jdx_image_info as rinfo right join jdx_image_comp as comp on rinfo.id=comp.remote_image_id and rinfo.type=1 left join jdx_image_info as cinfo on comp.image_id=cinfo.id and cinfo.type=0";
    char sql[2048]={0};
    sprintf(sql,"%s where rinfo.md5='%s' and rinfo.type=1 %s limit 0,20",pre,md5_remote,orderby);

    MYSQL_RES *rs=jp_select(sql);
    if(rs){
       while(MYSQL_ROW row=mysql_fetch_row(rs)){
            printf("%s和%s相似度:\n相似度1:%s\n相似度2:%s\n相似度3:%s\n相似度4:%s\n",row[1],row[3],row[4],row[5],row[6],row[7]);
       }
    }

    mysql_free_result(rs);
    jpmysql_close();

}

#endif // JPMYSQLLIB_H
